<?php
class xl_product{
    function read(){
        global $dbh;
        $sql='SELECT product.*,product_categories.alias AS category_alias FROM product,product_categories WHERE product.category_id=product_categories.id ORDER BY id DESC LIMIT 0,4';
        $sth=$dbh->prepare($sql);
        $sth->execute();
        return $sth->fetchAll();
    }
    function read_rand(){
        global $dbh;
        $sql='SELECT product.*,product_categories.alias AS category_alias FROM product,product_categories WHERE product.category_id=product_categories.id ORDER BY rand() LIMIT 0,7';
        $sth=$dbh->prepare($sql);
        $sth->execute();
        return $sth->fetchAll();
    }
    function read_id($id){
        global $dbh;
        $sql='SELECT product.*,product_categories.alias as product_categories_alias FROM product,product_categories WHERE product.category_id = product_categories.id AND product.alias=:id LIMIT 0,1';
        $sth=$dbh->prepare($sql);
        $sth->execute(array('id'=>$id));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function list_product($id){
        global $dbh;
        $sql='SELECT * FROM product WHERE alias=:id LIMIT 0,1';
        $sth=$dbh->prepare($sql);
        $sth->execute(array('id'=>$id));
        return $sth->fetchAll();
    }
    function read_alias($alias){
        global $dbh;
        $sql='SELECT review.*,product.alias,product.size FROM `review`,`product` where review.product_id=product.id AND alias=:alias';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':alias'=>$alias));
        return $sth->fetchAll();
    }
    function read_product_image($id){
        global $dbh;
        $sql='SELECT product_images.*,product.alias,product.day_time FROM product_images,product WHERE product_images.product_id=product.id AND alias=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetchAll();
    }
    function read_brand_product($id){
        global $dbh;
        $sql='SELECT name FROM brand_products WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function read_name($ip){
        global $dbh;
        $sql='SELECT name_user,day_time FROM review WHERE ip=:ip LIMIT 0,1';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':ip'=>$ip));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function insert_review($data){
        global $dbh;
        $sql='INSERT INTO review(product_id,name_user,ip,review,content,day_time) VALUE (:product_id,:name_user,:ip,:review,:content,:day_time)';
        $sth=$dbh->prepare($sql);
        $sth->execute($data);
        return $sth;
    }
    function read_review($product_id){
        global $dbh;
        $sql='SELECT * FROM review WHERE product_id=:product_id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array('product_id'=>$product_id));
        return $sth->fetchAll();
    }
    function read_id_alias($id){
        global $dbh;
        $sql='SELECT review.*,product.alias FROM review,product WHERE review.product_id=product.id AND review.product_id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function read_review_ip($ip,$product_id,$day_time){
        global $dbh;
        $sql="SELECT * FROM review where product_id=:product_id AND ip=:ip AND day_time LIKE :day_time LIMIT 0,1";
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':ip'=>$ip,':product_id'=>$product_id,':day_time'=>"%$day_time%"));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function list_product_id($id){
        global $dbh;
        $sql='SELECT product.* FROM product,product_categories WHERE product.category_id=product_categories.id AND product_categories.alias=:alias';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':alias'=>$id));
        return $sth->fetchAll();
    }
    function list_product_page($start,$id){
        global $dbh;
        $sql="SELECT product.* FROM product,product_categories WHERE product.category_id=product_categories.id AND product_categories.alias=:alias LIMIT $start,2";
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':alias'=>$id));
        return $sth->fetchAll();
    }
    function read_products($id,$start){
        global $dbh;
        $sql="SELECT product.*,product_categories.alias AS category_alias FROM product,product_categories WHERE product.category_id=product_categories.id AND product.alias LIKE :id LIMIT $start,3";
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>"%$id%"));
        return $sth->fetchAll(); 
    }
    function list_product_search($alias){
        global $dbh;
        $sql="SELECT COUNT(*) as count FROM product WHERE alias LIKE :alias";
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':alias'=>"%$alias%"));
        $search=$sth->fetch();
        return $search[0];
    }
    function list_parent($alias){
        global $dbh;
        $sql='SELECT id,parent,alias FROM product_categories WHERE alias=:alias LIMIT 0,1';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':alias'=>$alias));
        return $sth->fetch(PDO::FETCH_ASSOC); 
    }  
    function list_children($parent){
        global $dbh;
        $sql='SELECT * FROM product_categories WHERE parent=:parent ORDER BY id DESC';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':parent'=>$parent));
        return $sth->fetchAll(PDO::FETCH_ASSOC); 
    }
    function detail_children($id){
        global $dbh;
        $sql='SELECT * FROM product WHERE category_id=:id ORDER BY id DESC';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetchAll(PDO::FETCH_ASSOC); 
    }
      
}